package com.csi.dao.impl;

import com.csi.dao.HistoryDao;
import com.csi.domain.History;
import com.csi.pojo.PageBean;
import com.csi.utils.DateStringFormat;
import com.csi.utils.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class HistoryDaoImpl implements HistoryDao {
    @Override
    public void add(History history) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "INSERT INTO history(user_number,operation_type,pay_out,pay_in,amount,operation_time) VALUES (?,?,?,?,?,now())";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, history.getUserNumber());
        ps.setString(2, history.getOperationType());
        ps.setString(3, history.getPayOut());
        ps.setString(4, history.getPayIn());
        ps.setDouble(5, history.getAmount());

        ps.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public Double queryTodayAmount(String userNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT amount FROM history WHERE user_number = ? AND DATE_FORMAT(operation_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);

        ResultSet rs = ps.executeQuery();

        Double todayAmount = 0.0;

        while (rs.next()) {
            todayAmount += rs.getDouble("amount");
        }

        rs.close();
        ps.close();
        connection.close();

        return todayAmount;
    }

    @Override
    public List<History> queryAll(String userNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT * FROM history WHERE user_number = ?";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);

        ResultSet rs = ps.executeQuery();

        List<History> histories = new ArrayList<>();

        while (rs.next()) {
            History history = new History();

            history.setUserNumber(rs.getString("user_number"));
            history.setOperationType(rs.getString("operation_type"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            history.setAmount(rs.getDouble("amount"));
            history.setOperationTime(rs.getTimestamp("operation_time"));

            histories.add(history);
        }

        return histories;

    }

    @Override
    public List<History> queryByType(String userNumber, String operationType) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT * FROM history WHERE user_number = ? AND operation_type = ?";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setString(2, operationType);

        ResultSet rs = ps.executeQuery();

        List<History> histories = new ArrayList<>();

        while (rs.next()) {
            History history = new History();

            history.setUserNumber(rs.getString("user_number"));
            history.setOperationType(rs.getString("operation_type"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            history.setAmount(rs.getDouble("amount"));
            history.setOperationTime(rs.getTimestamp("operation_time"));

            histories.add(history);
        }

        return histories;
    }

    @Override
    public PageBean findPageByTime(Date date, int currentPageNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<History> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber); // 当前页

        String sql = """
                SELECT 
                    *
                FROM history
                WHERE operation_time >= ? AND operation_time < ?
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setDate(1, new java.sql.Date(date.getTime()));
        ps.setDate(2, new java.sql.Date(date.getTime() + 24 * 60 * 60 * 1000));
        ps.setInt(3, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(4, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        List<History> list = new ArrayList<>();
        while (rs.next()) {
            History history = new History();
            history.setId(rs.getInt("id"));
            history.setOperationTime(rs.getTimestamp("operation_time"));
            history.setOperationType(rs.getString("operation_type"));
            history.setAmount(rs.getDouble("amount"));
            history.setUserNumber(rs.getString("user_number"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            list.add(history);
        }


        ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE operation_time >= ? AND operation_time < ?");
        ps.setDate(1, new java.sql.Date(date.getTime()));
        ps.setDate(2, new java.sql.Date(date.getTime() + 24 * 60 * 60 * 1000));

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        pageBean.setDatas(list);

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public PageBean findPageAll(int currentPageNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<History> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber); // 当前页

        String sql = """
                SELECT 
                    *
                FROM history
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(2, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        List<History> list = new ArrayList<>();
        while (rs.next()) {
            History history = new History();
            history.setId(rs.getInt("id"));
            history.setOperationTime(rs.getTimestamp("operation_time"));
            history.setOperationType(rs.getString("operation_type"));
            history.setAmount(rs.getDouble("amount"));
            history.setUserNumber(rs.getString("user_number"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            list.add(history);
        }


        ps = connection.prepareStatement("SELECT COUNT(*) FROM history");

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        pageBean.setDatas(list);

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public PageBean findPageByUserNumber(int currentPageNumber, String userNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<History> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber); // 当前页

        String sql = """
                SELECT 
                    *
                FROM history
                WHERE user_number = ?
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setInt(2, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(3, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        List<History> list = new ArrayList<>();
        while (rs.next()) {
            History history = new History();
            history.setId(rs.getInt("id"));
            history.setOperationTime(rs.getTimestamp("operation_time"));
            history.setOperationType(rs.getString("operation_type"));
            history.setAmount(rs.getDouble("amount"));
            history.setUserNumber(rs.getString("user_number"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            list.add(history);
        }


        ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE user_number = ?");
        ps.setString(1, userNumber);

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        pageBean.setDatas(list);

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public List<String> findAllUserNumber() throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = """
                SELECT DISTINCT
                    user_number
                FROM history;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<String> list = new ArrayList<>();
        while (rs.next()) {
            String userNumber = rs.getString("user_number");
            list.add(userNumber);
        }

        DruidUtils.close(connection);

        return list;
    }

    @Override
    public List<History> queryByMonth(String userNumber, int year, int month) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = "SELECT * FROM history WHERE user_number = ? AND YEAR(operation_time) = ? AND MONTH(operation_time) = ?";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setInt(2, year);
        ps.setInt(3, month);

        ResultSet rs = ps.executeQuery();

        List<History> histories = new ArrayList<>();

        while (rs.next()) {
            History history = new History();

            history.setUserNumber(rs.getString("user_number"));
            history.setOperationType(rs.getString("operation_type"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            history.setAmount(rs.getDouble("amount"));
            history.setOperationTime(rs.getTimestamp("operation_time"));

            histories.add(history);
        }

        return histories;
    }

    @Override
    public PageBean findPageByType(int currentPageNumber, String userNumber, String operationType) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<History> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber); // 当前页

        String sql = """
                SELECT * FROM history 
                WHERE user_number = ? AND operation_type = ?
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, userNumber);
        ps.setString(2, operationType);
        ps.setInt(3, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(4, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        List<History> list = new ArrayList<>();
        while (rs.next()) {
            History history = new History();
            history.setId(rs.getInt("id"));
            history.setOperationTime(rs.getTimestamp("operation_time"));
            history.setOperationType(rs.getString("operation_type"));
            history.setAmount(rs.getDouble("amount"));
            history.setUserNumber(rs.getString("user_number"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            list.add(history);
        }


        ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE user_number = ? AND operation_type = ?");

        ps.setString(1, userNumber);
        ps.setString(2, operationType);

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        pageBean.setDatas(list);

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public PageBean findAllPageByType(int currentPageNumber, String operationType) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<History> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber); // 当前页

        String sql = """
                SELECT * FROM history 
                WHERE operation_type = ?
                LIMIT ?, ?;
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, operationType);
        ps.setInt(2, (currentPageNumber - 1) * pageBean.getPerPageNumber());
        ps.setInt(3, pageBean.getPerPageNumber());

        ResultSet rs = ps.executeQuery();

        List<History> list = new ArrayList<>();
        while (rs.next()) {
            History history = new History();
            history.setId(rs.getInt("id"));
            history.setOperationTime(rs.getTimestamp("operation_time"));
            history.setOperationType(rs.getString("operation_type"));
            history.setAmount(rs.getDouble("amount"));
            history.setUserNumber(rs.getString("user_number"));
            history.setPayOut(rs.getString("pay_out"));
            history.setPayIn(rs.getString("pay_in"));
            list.add(history);
        }


        ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE operation_type = ?");

        ps.setString(1, operationType);

        rs = ps.executeQuery();

        if (rs.next()) {
            pageBean.setTotalRecords(rs.getInt(1));
        }

        pageBean.setDatas(list);

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public Double findAmountByDate(Date date) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        String sql = """
                SELECT 
                    SUM(amount) AS total_amount
                FROM history
                WHERE operation_time >= ? AND operation_time < ?
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setDate(1, new java.sql.Date(date.getTime()));
        ps.setDate(2, new java.sql.Date(date.getTime() + 24 * 60 * 60 * 1000));

        ResultSet rs = ps.executeQuery();

        Double totalAmount = null;
        if (rs.next()) {
            totalAmount = rs.getDouble("total_amount");
        }

        DruidUtils.close(connection);

        return totalAmount;
    }

    @Override
    public Integer findNumberByDate(Date date) throws SQLException {
        Connection connection = DruidUtils.getConnection();


        PreparedStatement ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE operation_time >= ? AND operation_time < ?");
        ps.setDate(1, new java.sql.Date(date.getTime()));
        ps.setDate(2, new java.sql.Date(date.getTime() + 24 * 60 * 60 * 1000));

        ResultSet rs = ps.executeQuery();

        Integer number = null;
        if (rs.next()) {
            number = rs.getInt(1);
        }

        DruidUtils.close(connection);

        return number;
    }

    @Override
    public Map<Date, Double> findAmountsByMonth(int year, int month) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        Map<Date, Double> map = new TreeMap<>();
        Calendar calendar = Calendar.getInstance();
        calendar.set(year, month - 1, 1); // 设置年份和月份
        int days = calendar.getActualMaximum(Calendar.DAY_OF_MONTH); // 获取当月的天数
        for (int i = 1; i <= days; i++) {
            String date = String.format("%04d-%02d-%02d", year, month, i); // 格式化日期字符串

            PreparedStatement ps = connection.prepareStatement("SELECT SUM(amount) AS total_amount FROM history WHERE operation_time >= ? AND operation_time < ?");

            Date date1 = DateStringFormat.string2Date(date);

            ps.setDate(1, new java.sql.Date(date1.getTime()));
            ps.setDate(2, new java.sql.Date(date1.getTime() + 24 * 60 * 60 * 1000));

            ResultSet rs = ps.executeQuery();

            Double totalAmount = 0.0; // 如果当天没有数据则存为0
            if (rs.next()) {
                totalAmount = rs.getDouble("total_amount");
                if (totalAmount == null) {
                    totalAmount = 0.0;
                }
            }

            map.put(date1, totalAmount);
        }

        DruidUtils.close(connection);

        return map;
    }

    @Override
    public Map<String, Integer> findNumbersByYear(int year) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        Map<String, Integer> map = new TreeMap<>();
        Calendar calendar = Calendar.getInstance();
        calendar.set(year, 0, 1); // 设置年份为指定年份的1月1日
        for (int i = 1; i <= 12; i++) {
            String date = String.format("%04d-%02d", year, i); // 格式化日期字符串

            PreparedStatement ps = connection.prepareStatement("SELECT COUNT(*) FROM history WHERE operation_time >= ? AND operation_time <= ?");

            Date date1 = DateStringFormat.string2Date1(date + "-01");

            ps.setDate(1, new java.sql.Date(date1.getTime()));
            calendar.set(Calendar.MONTH, i - 1);
            calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
            ps.setDate(2, new java.sql.Date(calendar.getTimeInMillis() + 24 * 60 * 60 * 1000 - 1));

            ResultSet rs = ps.executeQuery();

            Integer number = null;
            if (rs.next()) {
                number = rs.getInt(1);
                if (number == null) {
                    number = 0;
                }
            }

            map.put(date, number);
        }

        DruidUtils.close(connection);

        return map;
    }

}
